using System;
using System.Text;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.Mvc.Ajax;
using MySql.Data.MySqlClient;
using System.Data;
using System.Configuration;

namespace TestProject
{
	public class User
	{
		private int m_user_ID = 0;
		private string m_username;
		private string m_password;
		private string m_email;
		private string m_firstname;
		private string m_lastname;
		private int m_level;
		
		public User()
		{
		}
		
		public User(string username, string password)
		{
	        IDbConnection dbcon;
	        dbcon = new MySqlConnection(Constants.connectionString);
	        dbcon.Open();
	        IDbCommand dbcmd = dbcon.CreateCommand();
			string statement = "SELECT user_id "
				+ "FROM users WHERE username = '" + username + "' and password = '" + password + "'";			
	        dbcmd.CommandText = statement;
	        IDataReader reader = dbcmd.ExecuteReader();
	        while(reader.Read()) {
				m_user_ID = (int) reader["user_id"];
	        }
	        reader.Close();
	        reader = null;
	        dbcmd.Dispose();
	        dbcmd = null;
	        dbcon.Close();
	        dbcon = null;
			getData();
		}
		
		public User(int user_ID)
		{
			if (user_ID!=0)
			{
				this.m_user_ID = user_ID;
				getData();
			}
		}
		
		public int user_ID
		{
			set {
				m_user_ID = value;
				getData();
			}
			get {
				return m_user_ID;
			}
		}
		
		public string username
		{
			set {
				m_username = value;
			}
			get {
				return m_username;
			}
		}
		
		public string firstname
		{
			set {
				m_firstname = value;
			}
			get {
				return m_firstname;
			}
		}
		
		public string lastname
		{
			set {
				m_lastname = value;
			}
			get {
				return m_lastname;
			}
		}
		
		public string password
		{
			set {
				m_password = value;
			}
			get {
				return m_password;
			}
		}
		
		public string email
		{
			set {
				m_email = value;
			}
			get {
				return m_email;
			}
		}
		
		public int level
		{
			set
			{
				m_level = value;
			}
			get
			{
				return m_level;
			}
		}
		
		private void getData()
		{
	        MySqlConnection conn = new MySqlConnection(Constants.connectionString);
	        try
			{
				conn.Open();
				string statement = "SELECT username, password, firstname, lastname, email, level "
					+ "FROM users WHERE user_id = '" + this.m_user_ID + "'";
		        MySqlCommand cmd = new MySqlCommand(statement, conn);			
		        MySqlDataReader reader = cmd.ExecuteReader();
		        while(reader.Read()) {
					m_username = (string) reader["username"];
					m_password = (string) reader["password"];
					m_firstname = (string) reader["firstname"];
					m_lastname = (string) reader["lastname"];
					m_email = (string) reader["email"];
					m_level = (int) reader["level"];
		        }
			}
			catch(Exception e)
			{
				Console.WriteLine(e.ToString());
			}
		    conn.Close();
		}
		
		/// <summary>
		/// Adds a new user to the database. The username of the new user must be unique from those
		/// store already or the user will not be added.
		/// </summary>
		/// <param name="username">
		/// A <see cref="System.String"/>
		/// </param>
		/// <param name="password">
		/// A <see cref="System.String"/>
		/// </param>
		/// <param name="firstname">
		/// A <see cref="System.String"/>
		/// </param>
		/// <param name="lastname">
		/// A <see cref="System.String"/>
		/// </param>
		/// <param name="email">
		/// A <see cref="System.String"/>
		/// </param>
		/// <param name="level">
		/// A <see cref="System.Int32"/>
		/// </param>
		/// <returns>
		/// A <see cref="System.Boolean"/> Returns false if username is already in the database.
		/// </returns>
		public static bool addUser(string username, string password, string firstname, string lastname, string email, int level)
		{
			MySqlConnection conn = new MySqlConnection(Constants.connectionString);
	        try
			{
				conn.Open();
				string statement = "SELECT * "
					+ "FROM users WHERE username = '" + username + "'";
		        MySqlCommand cmd = new MySqlCommand(statement, conn);			
		        MySqlDataReader reader = cmd.ExecuteReader();
				if (reader.HasRows)
				{			
					conn.Close();
					return false;	
				}
				
				string newStatement = "INSERT INTO users (username, password, firstname, lastname, email, level) "
					+ "VALUES ('"+username+"','"+password+"','"+firstname+"','"+lastname+"','"+email+"','"+level+"'";
		        MySqlCommand newCmd = new MySqlCommand(newStatement, conn);
				newCmd.ExecuteNonQuery();
			}
			catch(Exception e)
			{
				Console.WriteLine(e.ToString());
			}
		    conn.Close();
			return true;
		}
		
		/// <summary>
		/// Edits the information of the user having the user_ID provided. If username will be changed,
		/// this function first checks if username already exists in the database, if yes, function will fail.
		/// </summary>
		/// <param name="user_ID">
		/// A <see cref="System.Int32"/>
		/// </param>
		/// <param name="username">
		/// A <see cref="System.String"/>
		/// </param>
		/// <param name="firstname">
		/// A <see cref="System.String"/>
		/// </param>
		/// <param name="lastname">
		/// A <see cref="System.String"/>
		/// </param>
		/// <param name="email">
		/// A <see cref="System.String"/>
		/// </param>
		/// <param name="level">
		/// A <see cref="System.Int32"/>
		/// </param>
		/// <returns>
		/// A <see cref="System.Boolean"/> Returns false if new username already exists in the database.
		/// </returns>
		public static bool editUser(int user_ID, string username, string firstname, string lastname, string email, int level)
		{
			MySqlConnection conn = new MySqlConnection(Constants.connectionString);
	        try
			{
				conn.Open();
				string statement = "SELECT * "
					+ "FROM users WHERE username = '" + username + "' AND user_ID!='"+user_ID+"'";
		        MySqlCommand cmd = new MySqlCommand(statement, conn);			
		        MySqlDataReader reader = cmd.ExecuteReader();
				if (reader.HasRows)
				{			
					conn.Close();
					return false;	
				}
				
				string newStatement = "UPDATE users SET username='"+username+"', firstname='"+firstname+"', lastname='"+lastname
					+ "', email='"+email+"', level='"+level+"' WHERE user_ID='"+user_ID+"'";
		        MySqlCommand newCmd = new MySqlCommand(newStatement, conn);
				newCmd.ExecuteNonQuery();
			}
			catch(Exception e)
			{
				Console.WriteLine(e.ToString());
			}
		    conn.Close();
			return true;
		}
		
		/// <summary>
		/// Changes the password of the user with the given user_ID. 
		/// </summary>
		/// <param name="user_ID">
		/// A <see cref="System.Int32"/>
		/// </param>
		/// <param name="currentPassword">
		/// A <see cref="System.String"/> Old Password
		/// </param>
		/// <param name="newPassword">
		/// A <see cref="System.String"/> New Password
		/// </param>
		/// <returns>
		/// A <see cref="System.Boolean"/> Returns false if currentPassword does not match the old password of the user.
		/// </returns>
		public static bool changePassword(int user_ID, string currentPassword, string newPassword)
		{
			MySqlConnection conn = new MySqlConnection(Constants.connectionString);
	        try
			{
				conn.Open();
				string statement = "SELECT password "
					+ "FROM users WHERE user_ID = '" + user_ID + "'";
		        MySqlCommand cmd = new MySqlCommand(statement, conn);			
		        object result = cmd.ExecuteScalar();
				if (!currentPassword.Equals((string)result))
				{
					conn.Close();
					return false;
				}
				string newStatement = "UPDATE users SET password='"+newPassword+"' WHERE user_ID='"+user_ID+"'";
		        MySqlCommand newCmd = new MySqlCommand(newStatement, conn);
				newCmd.ExecuteNonQuery();
			}
			catch(Exception e)
			{
				Console.WriteLine(e.ToString());
			}
		    conn.Close();
			return true;
		}
		
		/// <summary>
		/// Get all the users stored in the database. 
		/// </summary>
		/// <returns>
		/// A <see cref="List<User>"/> All the users in the database in a List.
		/// </returns>
		public static List<User> getUsers()
		{
			List<User> usersList = new List<User>(0);
			MySqlConnection conn = new MySqlConnection(Constants.connectionString);
	        try
			{
				conn.Open();
				string statement = "SELECT user_id "
					+ "FROM users";
		        MySqlCommand cmd = new MySqlCommand(statement, conn);			
		        MySqlDataReader reader = cmd.ExecuteReader();
		        while(reader.Read()) {
					User current = new User((int) reader["user_id"]);
					usersList.Add(current);
		        }
			}
			catch(Exception e)
			{
				Console.WriteLine(e.ToString());
			}
		    conn.Close();
			return usersList;
		}
		
		/// <summary>
		/// Deletes the user with the user_ID given. Also deletes corresponding entries in the loan and reservations table. 
		/// </summary>
		/// <param name="user_ID">
		/// A <see cref="System.Int32"/>
		/// </param>
		public static void deleteUser(int user_ID)
		{
			MySqlConnection conn = new MySqlConnection(Constants.connectionString);
	        try
			{
				conn.Open();				
				string statement = "DELETE FROM users where user_ID='"+user_ID+"'";
		        MySqlCommand cmd = new MySqlCommand(statement, conn);
				cmd.ExecuteNonQuery();				
				string newStatement = "DELETE FROM loan where user_ID='"+user_ID+"'";
		        MySqlCommand newCmd = new MySqlCommand(newStatement, conn);
				newCmd.ExecuteNonQuery();				
				string newStatementAgain = "DELETE FROM loan where user_ID='"+user_ID+"'";
		        MySqlCommand newCmdAgain = new MySqlCommand(newStatementAgain, conn);
				newCmdAgain.ExecuteNonQuery();
			}
			catch(Exception e)
			{
				Console.WriteLine(e.ToString());
			}
		    conn.Close();
		}
		
		/// <summary>
		/// Searches the list of users using the parameters supplied. 
		/// </summary>
		/// <param name="username">
		/// A <see cref="System.String"/>
		/// </param>
		/// <param name="firstname">
		/// A <see cref="System.String"/>
		/// </param>
		/// <param name="lastname">
		/// A <see cref="System.String"/>
		/// </param>
		/// <param name="email">
		/// A <see cref="System.String"/>
		/// </param>
		/// <returns>
		/// A <see cref="List<User>"/> The searched users in a List.
		/// </returns>
		public static List<User> searchUsers(string username, string firstname, string lastname, string email)
		{
			StringBuilder statement = new StringBuilder("SELECT user_id FROM users WHERE");
			statement.Append(" username LIKE '%" + username + "%',");
			statement.Append(" firstname LIKE '%" + firstname + "%',");
			statement.Append(" lastname LIKE '%" + lastname + "%',");
			statement.Append(" email LIKE '%" + email + "%'");
			List<User> usersList = new List<User>(0);
			MySqlConnection conn = new MySqlConnection(Constants.connectionString);
	        try
			{
				conn.Open();
		        MySqlCommand cmd = new MySqlCommand(statement.ToString(), conn);			
		        MySqlDataReader reader = cmd.ExecuteReader();
		        while(reader.Read()) {
					User current = new User((int) reader["user_id"]);
					usersList.Add(current);
		        }
			}
			catch(Exception e)
			{
				Console.WriteLine(e.ToString());
			}
		    conn.Close();
			return usersList;
		}
	}
	
	public class Constants
	{
		private Constants()
		{
		}
		
		public static string connectionString =
	           "Server=" + ConfigurationSettings.AppSettings["DBHost"] + ";" +
	           "Database=" + ConfigurationSettings.AppSettings["DBName"] + ";" +
	           "User ID=" + ConfigurationSettings.AppSettings["DBUser"] + ";" +
	           "Password=" + ConfigurationSettings.AppSettings["DBPassword"] + ";" +
	           "Pooling=false";
	}
}


